#! -*- coding:utf-8 -*-
import MySQLdb
import pymongo
import codecs, sys
from pymongo import MongoClient

# 将文章转发追踪数据从mongodb复制到微信群管理系统的mysql数据库，自动执行
# wp_wechar_开头的表是传播跟踪的。
# wp_hh_开头的是医生管理
# wp_campaign_sign_in是签到相关的
# 本脚本写到wp_wechat_article_statistic

reload(sys)
sys.setdefaultencoding('utf-8')

conn = MongoClient("6dutcm.com", 27017)
mongodb = conn.health
mongodb.authenticate("hou", "hou@123")
content = mongodb.logarticle.find({"handled": {"$exists": False}})

# 打开数据库连接
db = MySQLdb.connect("118.178.87.163", "mysqladmin", "mysql2017!", "weiphp")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
index = 1
for i in content:
    # line=str(i['desc'].encode("GB18030"))
    uuid = i['_id']
    try:
        # 执行sql语句
        sql = "insert into wp_wechat_article_statistic(fromopenid,toopenid,articleid,ip,time)   values('" + i[
            'fromopenid'] + "','" + i['toopenid'] + "'," +str(index) + ",'" + i['ip'] + "','" + i[
                  'createtime'].strip() + "')"
        print sql
        # "select * from wp_manager_menu"  # "SELECT VERSION()"
        result = cursor.execute(sql)
        # 使用 fetchone() 方法获取一条数据库。
        # data = cursor.fetchone()

        print index, "handle data : ", result
        # 提交到数据库执行
        db.commit()
        mongodb.logarticle.update({"_id": uuid}, {"$set": {"handled": '1'}})
        index = index + 1
    except:
        # Rollback in case there is any error
        print "sql error"
        # db.rollback()
        break

# 关闭数据库连接
db.close()
